<?php

// Copyright (c) 2012 Mirza Adipradhana
//Menyatakan apakah DEBUGGER telah terLOAD
define("MYSQL_DBQUERY_EXECUTE_LOADED", true);

class DBQueryExecute extends MySqlDatabase {

    private $mInputData;
    private $mOperate;
    private $mPKValue;
    private $mDB;
    private $mTarget;
    private $mMultiForm = false;
    private $mCurrentPKValue;
    private $mAction;
    private $mIsRollBackSupport;
    private $mFormRollback;
    private $mIsAutoCommit = true;
    private $mIsAutoRedirect = true;
    private $mIsExecuteQuery = true;
    private $mAdditionalSyncCondition;
    private $mError = false;
    private $mErrorMessage;

    //MultiForm hanya berlaku untuk INSERT

    public function __construct($pRollBack = true) {
        $this->mIsRollBackSupport = $pRollBack;
        if ($this->mIsRollBackSupport === true)
            $this->mFormRollback = new FormRecovery($_POST);

        $this->host = DBHostName;
        $this->database = DBName;
        $this->user = DBUserName;
        $this->password = DBPassword;

        $this->ClearSQL();
        $this->ConnectDB();

        $this->mCurrentPKValue = $_POST['fCurrentPKValue'];

        //CREATE Instance Error Message
        $this->mErrorMessage = array();
    }

    public function SetSQL($pSQL) {
        $this->mSQL = $pSQL;
    }

    public function SetData($pInputData) {
        $this->mInputData = $pInputData;
    }

    public function SetAutoPK($pValue) {
        $this->mInputData[setting][autoPrimaryKey] = $pValue;
    }

    public function SetTableName($pTableName) {
        $this->mInputData[Table][Name] = $pTableName;
    }

    public function SetAutoRedirect($pIsAutoRedirect) {
        $this->mIsAutoRedirect = $pIsAutoRedirect;
    }

    public function SetExecuteQuery($pIsExecuteQuery) {
        //Jika bernilai true maka hasil GENERETE SQL langsung dieksekusi
        //Jika bernilai false maka outputnya berupa SQL
        $this->mIsExecuteQuery = $pIsExecuteQuery;
    }

    public function SetField($pDBFieldName, $pFormFieldName, $pPattern = false, $pRequestMethod = "post") {
        $tDebug = "";
        $tValidation = new InputValidation();

        if ($pRequestMethod == "get")
            $_POST[$pFormFieldName] = $_GET[$pFormFieldName];

        //CHECK: MULTIFORM COUNT, MAX <= TEMPLATE_SETTING_MAX_MULTIPLE_INPUT
        $tValidation->SetInput($_POST[fMultiFormCount]);
        $tValidation->SetPattern(pUnsignedInt);
        $mMultiFormCount = $tValidation->CheckValidation();
        if ($mMultiFormCount > TEMPLATE_SETTING_MAX_MULTIPLE_INPUT) {
            $mMultiFormCount = TEMPLATE_SETTING_MAX_MULTIPLE_INPUT;
            $_POST[fMultiFormCount] = TEMPLATE_SETTING_MAX_MULTIPLE_INPUT;
        }


        if ($this->mMultiForm) {
            //Jika mulitform maka berisi 1 < field <= MaxMultiple, bentuk nilai string separator ("|"), nantinya akan di konversi kedalam bentuk array
            for ($i = 1; $i <= ($mMultiFormCount <= TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? $mMultiFormCount : TEMPLATE_SETTING_MAX_MULTIPLE_INPUT); $i++) {
                if ($i > 1)
                    $this->mInputData[Field][$pDBFieldName].= '|';

                //$pFieldValue dalam multiple artinya nama form yang diberi akhiran _noMultiple
                //CHECK: terdapat Pattern Regex maka lakukan validasi ulang, jika tidak maka nilai diperbolehkan null
                $_POST[$pFormFieldName . '_' . $i] = trim($_POST[$pFormFieldName . '_' . $i]);

                if ($pPattern) {
                    $tValidation->SetInput($_POST[$pFormFieldName . '_' . $i]);
                    $tValidation->SetPattern($pPattern);
                    $tResult = $tValidation->CheckValidation();
                    if (is_bool($tResult) && !$tResult) {
                        $this->SetError(true);
                        $tDebug.= "<b>" . $pFormFieldName . " [" . $i . "]= " . $_POST[$pFormFieldName . '_' . $i] . "=>" . $tResult . "</b><br>";
                    } else {
                        $_POST[$pFormFieldName . '_' . $i] = $tResult;
                    }
                    $this->mInputData[Field][$pDBFieldName].= mysql_real_escape_string($_POST[$pFormFieldName . '_' . $i]);
                } else {
                    if (empty($_POST[$pFormFieldName . '_' . $i])) {
                        $_POST[$pFormFieldName . '_' . $i] = NULL;
                    }
                    $this->mInputData[Field][$pDBFieldName].= mysql_real_escape_string($_POST[$pFormFieldName . '_' . $i]);
                }
            }
        } else {
            //JIKA NULL maka tidak perlu escape string
            if (is_null($_POST[$pFormFieldName])) {
                $this->mInputData[Field][$pDBFieldName] = $_POST[$pFormFieldName];
            } else {
                if ($pPattern && !empty($_POST[$pFormFieldName])) {
                    $tValidation->SetInput($_POST[$pFormFieldName]);
                    $tValidation->SetPattern($pPattern);
                    $tResult = $tValidation->CheckValidation();

                    if ($tResult == false) {
                        $this->SetError(true);
                        $tDebug.= "<b>".$pPattern.' | ' . $pFormFieldName . "= " . $_POST[$pFormFieldName] . "=>" . $tResult . "<br>";
                    } else {
                        $_POST[$pFormFieldName] = $tResult;
                    }
                    $this->mInputData[Field][$pDBFieldName] = mysql_real_escape_string($_POST[$pFormFieldName]);
                } else {
                    $this->mInputData[Field][$pDBFieldName] = mysql_real_escape_string($_POST[$pFormFieldName]);
                }
            }
        }


        //DEBUG
        if (CORE_DEVELOPING_MODE) {
            echo "<div id=\"Debug\">" . $tDebug . "</div>";
        }
    }

    public function SetValiditas($pPattern) {
        $tValidation = new InputValidation();
        $tPattern = explode("|", $pPattern);

        if ($this->mMultiForm) {
            //Jika mulitform maka berisi 1 < field <= MaxMultiple, bentuk nilai string separator ("|"), nantinya akan di konversi kedalam bentuk array
            for ($i = 1; $i <= (TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? TEMPLATE_SETTING_MAX_MULTIPLE_INPUT : 1); $i++) {
                if ($i > 1)
                    $this->mInputData[Field][DB_NAMEFIELD_VALIDITAS].= '|';

                $tStr = 'Alfa7';
                unset($tValiditas);
                foreach ($tPattern as $tRes) {
                    $tStr.= mysql_real_escape_string($_POST[$tRes . '_' . $i]);
                }
                $tStr.= 'SeesternIT';
                $tValiditas = crc32($tStr);
                $tValiditas = sprintf("%u", $tValiditas);

                $this->mInputData[Field][DB_NAMEFIELD_VALIDITAS].= $tValiditas;
            }
        } else {
            $tStr = 'Alfa7';
            unset($tValiditas);
            foreach ($tPattern as $tRes) {
                //CHECK: String Langsung Disertakan / NUMERIC artinya index value
                $tStr.= $tRes;
            }
            $tStr.= 'SeesternIT';
            $tValiditas = crc32($tStr);
            $tValiditas = sprintf("%u", $tValiditas);

            $this->mInputData[Field][DB_NAMEFIELD_VALIDITAS] = $tValiditas;
        }
    }

    public function SetPrimaryKey($pDBFieldName, $pFormFieldName = null, $pPattern = false) {
        //CREATE PRIMARY KEY FIELD
        //AUTO PRIM KEY ONLY FOR INTEGER
        if ($this->mInputData[setting][autoPrimaryKey]) {
            //Inisialissasi variabel PrimaryKey
            $this->mInputData[PrimaryKey][$pDBFieldName] = "";
            $tPKVal = $this->GeneratePKValue();

            //SETUP Variable PrimaryKey
            if ($this->mMultiForm) {
                //Jika mulitform maka berisi 1 < field <= MaxMultiple, bentuk nilai string separator ("|"), nantinya akan di konversi kedalam bentuk array
                for ($i = 1; $i <= (TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? TEMPLATE_SETTING_MAX_MULTIPLE_INPUT : 1); $i++) {
                    if ($i > 1)
                        $this->mInputData[PrimaryKey][$pDBFieldName].= '|';

                    //$pFieldValue dalam multiple artinya nama form yang diberi akhiran _noMultiple
                    $this->mInputData[PrimaryKey][$pDBFieldName].= $tPKVal;
                    $tPKVal++;
                }
            }
            else {
                $this->mInputData[PrimaryKey][$pDBFieldName] = $tPKVal;
            }
            return $tPKVal;
        } else {
            if ($pFormFieldName != null) {
                if ($this->mMultiForm) {
                    //Jika mulitform maka berisi 1 < field <= MaxMultiple, bentuk nilai string separator ("|"), nantinya akan di konversi kedalam bentuk array
                    for ($i = 1; $i <= (TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? TEMPLATE_SETTING_MAX_MULTIPLE_INPUT : 1); $i++) {
                        if ($i > 1)
                            $this->mInputData[PrimaryKey][$pDBFieldName].= '|';

                        //$pFieldValue dalam multiple artinya nama form yang diberi akhiran _noMultiple
                        $this->mInputData[PrimaryKey][$pDBFieldName].= $_POST[$pFormFieldName . '_' . $i];
                    }
                }
                else {
                    $this->mInputData[PrimaryKey][$pDBFieldName] = $_POST[$pFormFieldName];
                }
            } else {
                echo "<div class=\"Debug\"><h3>ERROR:</h3><p>Auto Primary Key = false<br/> Form field name is undefined!</p></div>";
                die();
            }
        }
    }

    public function SetTargerURL($pValue) {
        $this->mTarget = $pValue;
    }

    public function SetMultiForm($pValue) {
        $this->mMultiForm = $pValue;
    }

    //$pData[PrimaryKey][$field] = $value: Nama Field yang menjadi Primary Key adalah Key dari ARRAY(PRIMARYKEY) dan Nilai Primary Key adalah value dari array tsb.


    public function GetPrimaryKey($pPK) {
        if (count($pPK) == 1) {
            foreach ($pPK as $tKey => $tVal) {
                $tRet[Field] = $tKey;
                $tRet[Value] = $tVal;
            }
        } else {
            //ERROR TIDAK ADA PK atau PK > 1
        }
        return $tRet;
    }

    private function getPKStructure(&$pPK, $pTable) {
        if ($pTable->BOF()) {
            while ($pTable->MoveNext()) {
                if ($pTable->Key == "PRI") {
                    //PERIKSA: Apakah PK FIELD yang diinputkan sesuai dengan PK yang ada di DATABASE
                    if ($pTable->Field == $pPK[Field]) {
                        $pPK['extra'] = $pTable->Extra;
                        $pPK['type'] = $pTable->Type;
                    } else {
                        //ERROR: PK FIELD yang diinputkan tidak sesuai dengan yang diinputkan
                    }
                    break;
                }
            }

            //PERIKSA: apakah Structure Field Extra = Auto Inc
            if ($pPK['extra'] == "auto_increment")
                $pPK['autoIncrement'] = true;
            else
                $pPK['autoIncrement'] = false;

            //PERIKSA FIELD DATA TYPE
            //PENJELASAN: Karena Struktur= "int(10) unsigned" explode '(' sehingga menjadi dua bagian: "int" dan "10) unsigned"
            $tType = explode('(', $pPK['type']);
            $pPK['fieldType'] = $tType[0];
        }
    }

    private function ConvertEmptyFieldToWhitespace(&$pData) {
        if (is_array($pData[Field])) {
            foreach ($pData[Field] as $tRes) {
                if (empty($tRes) && $tRes == "")
                    $tRes = "";
                $i++;
            }
        }
    }

    private function ConvertDataArrayToStringDML($pField) {
        $tResult = array();
        if (is_array($pField)) {
            $i = 0;
            foreach ($pField as $tKey => $tVal) {
                if (is_string($tVal)) {
                    $tResult[Field].= $tKey;
                    $tResult[Value].= '"' . $tVal . '"';
                } else if (is_int($tVal)) {
                    $tResult[Field].= $tKey;
                    $tResult[Value].= $tVal;
                } else if (is_null($tVal)) {
                    $tResult[Field].= $tKey;
                    $tResult[Value].= "NULL";
                } else {
                    $tResult[Field].= $tKey;
                    $tResult[Value].= '"' . $tVal . '"';
                }

                //JIKA: masih ada nilai, maka beri KOMA SEPARATOR
                if (($i + 1) < count($pField)) {
                    $tResult[Field].= ',';
                    $tResult[Value].= ',';
                }
                $i++;
            }
        } else {
            //ERROR: Data yang akan diconvert ke String untuk DML Database tidak memiliki Data Type ARRAY
        }
        return $tResult;
    }

    public function ConvertArrayKeyValueToEqualSeparator($pData, $pSeparator) {
        $mResult = null;
        $i = 0;
        foreach ($pData as $tKey => $tVal) {
            //NULL value support
            if (is_null($tVal))
                $mResult.= ' ' . $tKey . ' = NULL ';
            else
                $mResult.= ' ' . $tKey . ' = "' . $tVal . '"';
            if (($i + 1) < count($pData))
                $mResult.= ' ' . $pSeparator . ' ';
            $i++;
        }
        return $mResult;
    }

    public function SetPKValue($pValue) {
        $this->mPKValue = $pValue;
    }

    private function GeneratePKValue() {
        $pData = $this->mInputData;
        $mPK = $this->GetPrimaryKey($pData[PrimaryKey]);

        $tQuery = 'SELECT max(' . $mPK[Field] . ') as id FROM ' . $pData['Table']['Name']; //echo $tQuery;    
        $tDataPK = $this->Execute($tQuery);

        if ($tDataPK->Count() > 0) {
            //JIKA Table ADA ISI maka Nilai PK Tertinggi di ++;
            if ($tDataPK->MoveNext()) {
                $tMaxID = $tDataPK->id;
            } else {
                $tMaxID = 1;
            }
            //AMBIL NILAI MAX PK, lalu diincrement kan			
            $tMaxID++;
        } else {
            $tMaxID = 1;
        }
        return $tMaxID;
    }

    private function ValidationExecuteQueryInput($pData) {
        if (!empty($pData['Table']['Name']) && $pData[PrimaryKey] && $pData[data]) {
            return true;
        } else {
            return false;
        }
    }

    private function GenerateSQL() {
        $pData = $this->mInputData;
        //Ambil Nama Field beserta Value yang menjadi PK
        $tOperate = $this->mAction;
        $mPK = $this->GetPrimaryKey($pData[PrimaryKey]);
        //PERIKSA FIELD & VALUE
        //Jika ada yang kosong maka DIBER NILAI WHITESPACE ("")
        $this->ConvertEmptyFieldToWhitespace($pData);

        /*
          //PERIKSA AUTO PRIMARY KEY
          //PERIKSA: Structure Table untuk mengetahui Field yang merupakan PK beserta Type Field-nya dan status EXTREA: AUTO INC
          $tTableSelected = $this->Execute("DESCRIBE " . $pData[Table][Name]);
          $this->getPKStructure($mPK, $tTableSelected);

          //Jika Auto Primary Key adalah true, maka FIELD PRIMARY KEY harus INT
          if ($mPK['fieldType'] == "int" && $pData[setting][autoPrimaryKey] &&
          ($tPK[mPK] == null || empty($tPK[mPK]) || $tPK[mPK] == '')) {
          if (!CORE_DATABASE_AUTO_INC_SUPPORT || (CORE_DATABASE_AUTO_INC_SUPPORT && !$mPK['autoIncrement'])) {
          //jika AUTO INCREMENT Database = FALSE => PRIMARY KEY VALUE dibuat secara MANUAL
          //jika AUTO INCREMENT Database = TRUE => STRUCTURE FIELD EXTRA "HARUS" auto_increment. Jika tidak => PRIMARY KEY VALUE dibuat secara MANUAL
          $tMaxID = $this->GeneratePKValue();

          //INSERT: PK Field & VALUE ke $pData karena DATABASE TIDAK DAPAT MELAKUKAN AUTO INCREMENT SEHINGGA INSERT PUN DILAKUKAN SECARA MANUAL
          $pData[Field][$mPK[Field]] = $tMaxID; //print_r($pData);
          } else if (!$mPK['autoIncrement']) {
          //ERROR: Tabel '.$pData[Table][Name].' pada '.$mPK[Field].', STRUCTURE FIELD EXTRA belum diset ke auto_increment
          }
          } else {
          if ($mPK['fieldType'] != "int") {
          //ERROR: Auto PK tidak bertipe INT. Tidak dapat di AUTO INCREMENT
          }
          }
         */

        //CONVERT: Table FIELD on ARRAY data type TO STRING WITH COMA(,) SEPARATOR
        $tInput = $this->ConvertDataArrayToStringDML($pData[Field]);

        // ===============================================================================================================
        // GENERATE SQL
        if ($tOperate == 'add') {
            //generate SQL INSERT
            $mSQL = 'INSERT INTO ' . $pData['Table']['Name'];


            if ($this->mMultiForm) {
                //Jika multiform => multiple input data
                //KONVERSI: nilai value menjadi bentuk array
                $tInput[Value] = str_replace('"', '', $tInput[Value]);
                $tTemp = explode(",", $tInput[Value]);
                $x = 0;
                $tValue = array();

                //KONVERSI KEDALAM BENTUK NESTED ARRAY
                while (isset($tTemp[$x])) {
                    $tTempValue[$x] = explode("|", $tTemp[$x]);
                    $y = 0;
                    while (isset($tTempValue[$x][$y])) {

                        if (isset($tValue[$y]))
                            $tValue[$y].= ", ";

                        //SETIAP NILAI EMPTY secara defualt -> NULL
                        if ($tTempValue[$x][$y] == "")
                            $tValue[$y].= "NULL";
                        else if (is_null($tTempValue[$x][$y]))
                            $tValue[$y].= "NULL";
                        else
                            $tValue[$y].= '"' . $tTempValue[$x][$y] . '"';
                        //echo '"' . $tTempValue[$x][$y] . '" <br>';
                        $y++;
                    }
                    $x++;
                }

                $tPK = explode("|", $mPK[Value]);
                $mSQL.= ' (' . mysql_real_escape_string($mPK[Field]) . ', ' . mysql_real_escape_string($tInput[Field]) . ') VALUES ';
                for ($i = 1; $i <= (TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? TEMPLATE_SETTING_MAX_MULTIPLE_INPUT : 1); $i++) {
                    if ($tPK[$i - 1] && $tValue[$i - 1]) {
                        if ($i > 1)
                            $mSQL.= ', ';
                        $mSQL.= '("' . $tPK[$i - 1] . '", ' . $tValue[$i - 1] . ')';
                    }
                }
            }
            else {
                $mSQL.= ' (' . $mPK[Field] . ', ' . $tInput[Field] . ') ';
                $mSQL.= ' VALUES (' . (is_string($mPK[Value]) ? '"' . $mPK[Value] . '"' : $mPK[Value]) . ', ' . $tInput[Value] . ')';
            }
        } else if ($tOperate == 'edt') {
            //generate SQL UPDATE
            //PK FIELD dan PK VALUE tidak boleh Kosong!
            if (!empty($mPK[Field]) && $mPK[Value]) {
                //SETUP CurrentPKValue jika belum diset
                //Belum diset artinya PK value tidak berubah, maka nilainya sama dengan yang sudah ada pada DB
                //echo 'CurPKVal = '.$this->mCurrentPKValue;
                if (empty($this->mCurrentPKValue)) {
                    $this->mCurrentPKValue = $mPK[Value];
                }

                $tUpdateData = $this->ConvertArrayKeyValueToEqualSeparator($pData[Field], ', ');
                //$this->ConvertDataStringDMLToUpdateDML($tInput);

                $mSQL = 'UPDATE ' . $pData['Table']['Name'] . ' SET  ';
                //CHECK: Jika nilai PK berubah maka PK disertakan
                if ($this->mCurrentPKValue !== $mPK[Value])
                    $mSQL.= '' . $mPK[Field] . ' = "' . $mPK[Value] . '", ';

                $mSQL.= $tUpdateData . ' ';

                //SETUP: KONDISI
                //JIKA nilai PK baru (PK berubah) maka gunakan nilai PK lama untuk SYNC
                if ($this->mCurrentPKValue !== $mPK[Value])
                    $mSQL.= 'WHERE ' . $mPK[Field] . ' = "' . $this->mCurrentPKValue . '" ';
                else
                    $mSQL.= 'WHERE ' . $mPK[Field] . ' = "' . $mPK[Value] . '" ';
            } else {
                //ERROR: PK FIELD dan PK VALUE KOSOMG
            }
        } else if ($tOperate == 'del') {
            //generate SQL UPDATE
            //PK FIELD dan PK VALUE tidak boleh Kosong!
            if (!empty($mPK[Field]) && $mPK[Value]) {
                $mSQL = 'DELETE FROM ' . $pData['Table']['Name'] . ' ';
                $mSQL.= 'WHERE (' . $mPK[Field] . '= "' . $mPK[Value] . '")';
            } else {
                //ERROR: PK FIELD dan PK VALUE KOSOMG
            }
        }

        if ($this->mAdditionalSyncCondition && $tOperate == 'edt')
            $mSQL = $mSQL . $this->mAdditionalSyncCondition;

        $mSQL = $mSQL . '; ';

        if (CORE_DEVELOPING_MODE)
            echo '<div id="debug" class="generate_sql"><h3>AUTO GENERATE SQL:</h3><p>' . $mSQL . '<p></div>';

        return $mSQL;
    }

    private function GetMultiFormCount() {
        $mValidation = new InputValidation();

        $mValidation->SetInput($_POST['fMultiFormCount']);
        $mValidation->SetPattern(pUnsignedInt);
        $tResult = $mValidation->CheckValidation();

        if ($tResult > MAX_MULTIPLE_INPUT)
            $tResult = MAX_MULTIPLE_INPUT;
        else if (!$tResult)
            $tResult = 1;
        return $tResult;
    }

    private function IsDataIdentic() {
        //KHUSUS ACTION ADD dengan AUTO INCREMENT PK
        $pData = $this->mInputData;
        $mPK = $this->GetPrimaryKey($pData[PrimaryKey]);

        //PERIKSA FIELD & VALUE
        $this->ConvertEmptyFieldToWhitespace($pData);

        $mSQLIdentic = null;
        $tStrDML = $this->ConvertDataArrayToStringDML($pData[Field]);

        //GENERATE FIELD
        $tField.= $mPK[Field];
        if ($tStrDML[Field])
            $tField.= ', ' . $tStrDML[Field];

        //SETUP VALUE
        $i = 0;
        while ($i < $this->GetMultiFormCount()) {
            if ($i != 0)
                $mSQL.= " OR ";

            $mSQL.= "(";
            $j = 0;
            foreach ($pData[Field] as $tFieldName => $tFieldValue) {
                if ($j != 0)
                    $mSQL.= " AND ";

                $tTempValue = explode("|", $tFieldValue);
                $mSQL.= $tFieldName . ' = "' . $tTempValue[$i] . '"';
                $j++;
            }
            $mSQL.= ") ";
            $i++;
        }


        $mSQLIdentic = 'SELECT ' . $tField . ' FROM  ' . $pData['Table']['Name'] . ' WHERE ' . $mSQL;


        //DEBUGGER
        if (CORE_DEVELOPING_MODE)
            echo '<div class="Debug generate_sql"><h3>IS IDENTIC SQL:</h3><p>' . $mSQLIdentic . '<p></div>';


        //EXEKUSI: SYNC Table
        $tResultIdentic = $this->Execute($mSQLIdentic);
        if ($tResultIdentic->Count() > 0) {
            $this->SetError(true);
            $this->mErrorMessage[ERROR_SECTION] = "DATABASE";
            $this->mErrorMessage[ERROR_CODE] = "INSERT_IDENTIC_DATA";
            return true;
        } else {
            return false;
        }
    }

    public function SetAdditionalSyncCondition($pSyncCondition) {
        $this->mAdditionalSyncCondition = $pSyncCondition;
    }

    private function SyncTableBeforeExecute() {
        $pData = $this->mInputData;
        $mPK = $this->GetPrimaryKey($pData[PrimaryKey]);
        $tPK = explode("|", $mPK[Value]);

        //SYNC PRIMARY KEY VALUE, khususnya untuk MULTIFORM dan PK diinput secara manual untuk menghindari duplikasi
        for ($i = 1; $i <= $this->GetMultiFormCount(); $i++) {
            //REGENERATE ARRAY
            // Buat array baru dengan value tanpa nilai yang sedang/akan diuji
            //echo $tPK[$i - 1] . '<br>';echo "<br><b>".$tPK[($i-1)]."</b>"; echo "<br>ARRAY "; print_r($tPK);

            unset($temp);
            $temp = array();
            for ($x = 0; $x < $this->GetMultiFormCount($pData[Field]); $x++) {
                if ($x != ($i - 1))
                    array_push($temp, $tPK[$x]);
            }

            //LAKUKAN pencocokan
            if (array_search($tPK[$i - 1], $temp)) {
                //JIKA ADA DUPLIKASI
                $this->SetError(true);
                $this->mErrorMessage[ERROR_SECTION] = "DATABASE";
                $this->mErrorMessage[ERROR_CODE] = "MULTIFORM_DUPLICATE_PRIMARY_KEY";

                //LAKUKAN ROLLBACK SEGERA JIKA NOT AUTOCOMMIT
                if (!$this->IsAutocommit()) {
                    echo 'ROLLING BACK ACTION';
                    $this->Rollback();
                    echo '...ok<br>';
                }
                break;
            } else {
                
            }
        }


        //CREATE SYNC DATABASE
        $mSQLSync = null;
        $tStrDML = $this->ConvertDataArrayToStringDML($pData[Field]);

        $tField.= $mPK[Field];
        if ($tStrDML[Field])
            $tField.= ', ' . $tStrDML[Field];
        //CHECK: apakah PRIMARY KEY acuan lebih dari satu (MULTIPLE FORM INPUT)
        for ($i = 1; $i <= (TEMPLATE_SETTING_MAX_MULTIPLE_INPUT ? TEMPLATE_SETTING_MAX_MULTIPLE_INPUT : 1); $i++) {
            if ($tPK[$i - 1]) {
                //ACTION EDIT: tidak ada multiform sehingga nilai PK tunggal
                if ($this->mAction == "edt") {
                    ////
                    //SETUP CurrentPKValue jika belum diset
                    //Belum diset artinya PK value tidak berubah, maka nilainya sama dengan yang sudah ada pada DB
                    if (empty($this->mCurrentPKValue)) {
                        $this->mCurrentPKValue = $tPK[$i - 1];
                    }
                    ////
                    //JIKA nilai PK baru (PK berubah) maka gunakan nilai PK lama untuk SYNC
                    if ($this->mCurrentPKValue !== $tPK[$i - 1])
                        $tSQL_PK.= ' ' . $mPK[Field] . ' = "' . $this->mCurrentPKValue . '" ';
                    else
                        $tSQL_PK.= ' ' . $mPK[Field] . ' = "' . $tPK[$i - 1] . '" ';
                }
                else {
                    //ACTION ADD: diperbolehkan PK multi. Pada action add nilai PK harus dipastikan tidak ada
                    if ($i > 1)
                        $tSQL_PK.= ' OR ';

                    //JIKA PRIMARY KEY AUTO MAKA: pengecekan tidak pada PK nya namun pada nilainya, jika identik maka dianggap duplikasi
                    $tSQL_PK.= ' ' . $mPK[Field] . ' = "' . $tPK[$i - 1] . '" ';
                }
            }
        }


        $mSQLSync = 'SELECT ' . $tField . ' FROM  ' . $pData['Table']['Name'] . ' ';
        if (!empty($mPK[Field]) && !empty($mPK[Value])) {
            //JIKA: PRIMARY KEY & VALUENYA ISSET
            $mSQLSync.='WHERE ' . $tSQL_PK . ' ';
        } else if ($pData[Table][condition]) {
            //JIKA: PRIMARY KEY & VALUENYA NOTSET, Table CONDITION ISSET
            $mSQLSync.= ' WHERE ' . $tSQL_PK . ' ';
            if (is_array($pData[Table][condition])) {
                //JIKA: Table CONDITION ARRAY
                $mSQLSync.= $this->ConvertArrayKeyValueToEqualSeparator($pData[Table][condition], 'AND');
            } else {
                //JIKA: Table CONDITION bukan ARRAY
                $mSQLSync.= $pData[Table][condition];
            }
        } else {
            //ERROR: SYNC DATA SEBELUM DILAKUKAN EKSEKUSI TIDAK DAPAT DILAKUKAN. Penyebabnya:
            // 1) Primary Key FIELD dan VALUE belum didefinisikan
            // 2) No 1: False && Tidak ada kondisi tabel yang didefinisikan
        }

        //CHECK: Apakah ada kondisi sinkronisasi tambahan
        if ($this->mAdditionalSyncCondition) {
            $mSQLSync.= $this->mAdditionalSyncCondition;
        }

        //DEBUGGER
        if (CORE_DEVELOPING_MODE)
            echo '<div class="Debug generate_sql"><h3>SYNC SQL:</h3><p>' . $mSQLSync . '<p></div>';


        //EXEKUSI: SYNC Table
        $tResultSync = $this->Execute($mSQLSync);

        return $tResultSync;
    }

    private function CreateSQLReviewExecutedSQL($pData, $pOperate) {
        //Method untuk membuat SQL yang digunakan untuk memeriksa apakah data yang telah dieksekusi telah tereksekusi dengan benar
        $mSQLChecker = null;
        //Ubah data berbentuk attay menjadi data yang siap untuk diolah menjadi DML (OUTPUT: field, value)
        $tStrDML = $this->ConvertDataArrayToStringDML($pData[Field]);
        $mPK = $this->GetPrimaryKey($pData[PrimaryKey]);

        $mSQLChecker = 'SELECT ' . $tStrDML[Field] . ' FROM  ' . $pData['Table']['Name'] . ' ';

        //KONDISI untuk OPERASI ADD berbeda dengan (EDIT/DELETE), dimana
        if ($pOperate == 'add') {
            //ADD melihat dari nilai yang diinputkan
            $mSQLChecker.= ' WHERE ';
            if (is_array($pData[Field])) {
                //JIKA: Table FIELD(DATA YANG DIINPUTKAN) ARRAY
                $mSQLChecker.= $this->ConvertArrayKeyValueToEqualSeparator($pData[Field], 'AND');
            } else {
                //JIKA: DATA TIDAK DITEMUKAN
                //ERROR:
            }
        } else if ($pOperate == 'edt' || $pOperate == 'del') {
            //EDIT/DELETE melihar dair primary key nya
            if (!empty($mPK[Field]) && !empty($mPK[Value])) {
                //JIKA: PRIMARY KEY & VALUENYA ISSET
                $mSQLChecker.='WHERE ' . $mPK[Field] . '= \'' . $mPK[Value] . '\' ';
                if (is_array($pData[Field])) {
                    //JIKA: Table FIELD(DATA YANG DIINPUTKAN) ARRAY
                    $mSQLChecker.= ' AND '; //Tambahkan connector kondisi
                    $mSQLChecker.= $this->ConvertArrayKeyValueToEqualSeparator($pData[Field], 'AND');
                }
            } else if ($pData[Table][condition]) {
                //JIKA: PRIMARY KEY & VALUENYA NOTSET, Table CONDITION ISSET
                $mSQLChecker.= ' WHERE ';
                if (is_array($pData[Table][condition])) {
                    //JIKA: Table CONDITION ARRAY
                    $mSQLChecker.= $this->ConvertArrayKeyValueToEqualSeparator($pData[Table][condition], 'AND');
                } else {
                    //JIKA: Table CONDITION bukan ARRAY
                    $mSQLChecker.= $pData[Table][condition];
                }
            } else {
                //ERROR: SYNC DATA SEBELUM DILAKUKAN EKSEKUSI TIDAK DAPAT DILAKUKAN. Penyebabnya:
                // 1) Primary Key FIELD dan VALUE belum didefinisikan
                // 2) No 1: False && Tidak ada kondisi tabel yang didefinisikan
            }
        }

        return $mSQLChecker;
    }

    public function ReviewExecutedSQL($pOperate) {
        $tInputData = $this->mInputData;
        $tResult = false;
        $pSQLChecker = $this->CreateSQLReviewExecutedSQL($tInputData, $pOperate);

        //Lakukan Eksekusi SQLChecker untuk melakukan Pemeriksaan Data yang dimanipulasi
        $tResult = $this->Execute($pSQLChecker);
        $tCount = $tResult->Count();

        //Lakukan pemeriksaan sesuai dengan operasi yang dilakukan
        if ($pOperate == 'add' || $pOperate == 'edt') {
            //Khusus untuk operasi add, jika hasil outputnya adalah 1 maka operasi berhasil
            if ($tCount == 1)
                return true;
            else
                return false;
        }
        else {
            if ($tCount < 0)
                return true;
            else
                return false;
        }
    }

    public function ExecuteSQL($pSQL, $pOperate) {
        $tIsOpen = $this->IsOpen();
        $pTargetURL = $this->mTarget;
        //PERIKSA: koneksi dengan database. Reconnect DB		
        if (!$tIsOpen) {
            //Lakukan Reconnect jika Status Koneksi = 0
            $this->ConnectDB();
        }

        //Lakukan Eksekusi SQL
        $tResult = $this->Execute($pSQL);

        //ERROR HANDLER
        $tResult = $this->ReviewExecutedSQL($pOperate);
    }

    public function ExecuteQuery($pOperate, $pAutoCommit = true) {
        /* 		Deskripsi:
          $pData berisi tentang input data

          $pData[Table][Name] : Nama Tabel
          $pData[Table][condition] : Kondisi Tabel
          $pData[PrimaryKey][$field] = $value: Nama Field yang menjadi Primary Key adalah Key dari ARRAY(PRIMARYKEY) dan Nilai Primary Key adalah value dari array tsb.
          $pData[Field][$namaField] = $value


          SETTING
          ===========
          $pData[setting][autoPrimaryKey] :  bernilai true jika AUTO INCREMENT pada Primary Key
         */

        //INITIALIZATION
        //==============
        $this->mIsAutoCommit = $pAutoCommit;
        $this->mAction = $pOperate;
        $tInputData = $this->mInputData;



        //CREATING FORM REDIRECT
        $mRedirect = new Redirector($this->mTarget);
        $mRedirect->SetId($this->mPKValue);
        //if ($pAutoCommit)
        //$mRedirect->PrintFormRedirect();


        $tIsOpen = $this->IsOpen();
        //PERIKSA: koneksi dengan database. Reconnect DB		
        if (!$tIsOpen) {
            //Lakukan Reconnect jika Status Koneksi = 0
            $this->ConnectDB();
        }


        //validasi apakah seluruh data yang diperlukan telah ada. Jika tidak KELUAR(return;) dari Mehtod
        //if(!$this->ValidationExecuteQueryInput($this->mInputData))
        //return;
        // ==============================================================================================
        //  SYNCRONIZE Table: periksa tabel sebelum data dieksekusi
        $mSync = $this->SyncTableBeforeExecute();


        //KHUSUS ACTION ADD & AUTO PK VALUE
        if ($this->mAction == "add") {
            $mIsIdentic = $this->IsDataIdentic();
        }


        // ==============================================================================================
        //  GENERATE SQL FROM QUERY DATA INPUT
        $this->mSQL = $this->GenerateSQL(); //echo $this->mSQL;
        // ==============================================================================================
        //  EXECUTE SQL	
        //JIKA SAFE EXECUTE QUERY, lakukan begin - commit
        if (!$pAutoCommit) {
            $this->Begin();
        }

        //PERIKSA APAKAH ADA ERROR ($this->mError = true)
        //JANGAN LAKUKAN JIKA ADA ERROR
        if (!$this->mError) {
            switch ($this->mAction) {
                case 'add': {
                        $tCountSync = $mSync->Count();
                        if (empty($tCountSync)) {
                            //JIKA: hasil SYNC adalah 0 maka eksekusi dapat dilakukan.
                            if ($this->mIsExecuteQuery)
                                $this->ExecuteSQL($this->mSQL, $pOperate);
                            else
                                return $this->mSQL;

                            $tRet = true;
                        } else {
                            $this->mErrorMessage[ERROR_SECTION] = "DATABASE";
                            $this->mErrorMessage[ERROR_CODE] = "DUPLICATE_PRIMARY_KEY";

                            //LAKUKAN ROLLBACK SEGERA JIKA NOT AUTOCOMMIT
                            if (!$this->IsAutocommit()) {
                                echo 'ROLLING BACK ACTION';
                                $this->Rollback();
                                echo '...ok<br>';
                            }
                            $tRet = false;
                        }
                        break;
                    }
                case 'edt': {
                        $tCountSync = $mSync->Count();
                        if (!empty($tCountSync)) {
                            //JIKA: hasil SYNC adalah !=0 maka eksekusi dapat dilakukan 
                            //		karena EKSEKUSI UPDATE harus ada data yang akan diUpdate.
                            $this->ExecuteSQL($this->mSQL, $pOperate);
                            $tRet = true;
                        } else {
                            $tRet = false;
                        }
                        break;
                    }
                case 'del': {
                        $tCountSync = $mSync->Count();
                        if (!empty($tCountSync)) {
                            //JIKA: hasil SYNC adalah !=0 maka eksekusi dapat dilakukan 
                            //		karena EKSEKUSI UPDATE harus ada data yang akan diUpdate.
                            $this->ExecuteSQL($this->mSQL, $pOperate);
                            $tRet = true;
                        } else {
                            $tRet = false;
                        }
                        break;
                    }
                default: {

                        break;
                    }
            }

//JIKA: Tidak Safe Executing SQL, maka akan dilakukan eksekusi sql COMMIT setiap selesai melakukan DML untuk memastikan data termanipulasi dalam database 
            if ($pAutoCommit) {
                $this->Commit();
                //DISCONNECT SQL
                //$this->Close();
            }


            if ($tRet) {
                //REDIRECTING, hanya berlaku NONSAFE EXECUTE QUERY
                if ($pAutoCommit && $this->mIsAutoRedirect) {
                    //Jika multiple input form maka PK untuk redirect tidak diperlukan karena PK target harus unique
                    if (!$this->mMultiForm) {
                        $mPK = $this->GetPrimaryKey($this->mInputData[PrimaryKey]);
                        $tPK[Name] = $mPK[Field];
                        $tPK[Value] = $mPK[Value];
                    } else {
                        $tPK[Name] = "null";
                        $tPK[Value] = "null";
                    }

                    //REDIRECT!!
                    if (CORE_DEVELOPING_MODE) {
                        $mRedirect->Redirecting();
                    } else {
                        $mRedirect->Redirecting($mPK);
                    }
                }
            } else {
                //GAGAL
                //if ($pAutoCommit) {
                if ($this->mIsRollBackSupport === true) {
                    $this->mFormRollback->SetErrorSection($this->mErrorMessage[ERROR_SECTION]);
                    $this->mFormRollback->SetErrorCode($this->mErrorMessage[ERROR_CODE]);
                    $this->mFormRollback->PrintForm();

                    if (CORE_DEVELOPING_MODE) {
                        echo "<div class=\"Debug\"><h3 class=\"Error\">An error occured:</h3>" . $this->mErrorMessage->GetErrorMessage() . "</div>";
                        echo "<input type=\"button\" id=\"RollbackButton\" class=\"button\" value=\"Rollback\" />\n\n";
                    } else {
                        //DO WHILE ERROR: NON DEV MODE
                    }
                }
                //}
            }
        } else {
            if ($this->mIsRollBackSupport === true) {
                $this->mFormRollback->SetErrorSection($this->mErrorMessage[ERROR_SECTION]);
                $this->mFormRollback->SetErrorCode($this->mErrorMessage[ERROR_CODE]);
                $this->mFormRollback->PrintForm();

                if (CORE_DEVELOPING_MODE) {
                    echo "<div class=\"Debug\"><h3 class=\"Error\">An error occured:</h3>" . $this->mErrorMessage->GetErrorMessage() . "</div>";
                    echo "<input type=\"button\" id=\"RollbackButton\" class=\"button\" value=\"Rollback\" />\n\n";
                } else {
                    //DO WHILE ERROR: NON DEV MODE
                }
            }
            $tRet = false;
        }


        return $tRet;
    }

    /* ===== *//* ===== *//* ===== *//* ===== *//* ===== *//* ===== *//* ===== */
    /*                       E R R O R   H A N D L E R                         */
    /* ===== *//* ===== *//* ===== *//* ===== *//* ===== *//* ===== *//* ===== */

    private function SetError($pBool) {
        $this->mError = $pBool;
    }

    public function SetErrorMessage($_ERRORS) {
        $this->mErrorMessage = $_ERRORS;
    }

    public function GetError() {
        //GET: apakah terdapat error
        $tErrorRes = $this->RetrieveError();
        if ($tErrorRes !== false)
            return $tErrorRes;
        else
            return $this->mError;
    }

    private function RetrieveError() {
        //RETRIEVE ERROR CODE
        if ($this->mError) {
            $_ERRHANDLER[ERROR_SECTION] = $this->mErrorMessage[ERROR_SECTION];
            $_ERRHANDLER[ERROR_CODE] = $this->mErrorMessage[ERROR_CODE];

            return $_ERRHANDLER;
        } else {
            return false;
        }
    }

}